USE [simulasyon]
GO

/****** Object:  UserDefinedFunction [dbo].[WorkTime]    Script Date: 04/24/2011 18:42:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Baran Kaynak
-- Create date: 14.03.2011
-- Description:	09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime] 
(
	@StartDate DATETIME,
	@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
	DECLARE @Temp BIGINT
	SET @Temp=0
	
	DECLARE @FirstDay DATE
	SET @FirstDay = CONVERT(DATE, @StartDate, 112)
	
	DECLARE @LastDay DATE
	SET @LastDay = CONVERT(DATE, @FinishDate, 112)
	
	DECLARE @StartTime TIME
	SET @StartTime = CONVERT(TIME, @StartDate)
	
	DECLARE @FinishTime TIME
	SET @FinishTime = CONVERT(TIME, @FinishDate)
	
	DECLARE @WorkStart TIME
	SET @WorkStart = '09:30'
	
	DECLARE @WorkFinish TIME
	SET @WorkFinish = '17:30'
	
	IF (@StartTime<@WorkStart)
	BEGIN
		SET @StartTime = @WorkStart
	END
	IF (@FinishTime>@WorkFinish)
	BEGIN
		SET @FinishTime=@WorkFinish
	END
	
	DECLARE @CurrentDate DATE
	SET @CurrentDate = CONVERT(DATE, @StartDate, 112)
	DECLARE @LastDate DATE
	SET @LastDate = CONVERT(DATE, @FinishDate, 112)
	
	WHILE(@CurrentDate<=@LastDate)
	BEGIN		
		IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
		BEGIN
			IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
			BEGIN
				SET @Temp = (@Temp + (9*60))
			END
			--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
			ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
			BEGIN
				SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
			END
			
			ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
			BEGIN
				SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
			END
			--IF it starts and finishes in the same date
			ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
			BEGIN
				SET @Temp = DATEDIFF(MINUTE, @StartDate, @FinishDate)
			END
		END
		SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
	END
	
	-- Return the result of the function
	IF @Temp<0
	BEGIN
		SET @Temp=0
	END
	RETURN @Temp

END

GO

